const mysql = require('mysql');
const zlib = require('zlib');

var buffInflate = function (buff) {
    if (!(buff instanceof Buffer)) return buff;
    let inflate = zlib.inflateSync(buff).toString();
    return inflate;
}
var strDeflate = function (str) {
    if (typeof (str) !== 'string') return str;
    let buff = new Buffer(str);
    let deflate = zlib.deflateSync(buff);
    return deflate;
}

var getMysqlCfg = function (req) {
    let mysqlCfg = {
        host: req.host || '127.0.0.1',
        port: Number(req.port) || 3306,
        user: req.user || 'root',
        password: req.password || 'test',
        database: req.database || 'lotr_trunk1',
    };

    return mysqlCfg;
}

var selectSql = async function (req) {
    var getHandler = function (req) {
        if (!req.select)
            return {};
        let select = req.select.split(';');
        let handler = {};
        for (let v of select) {
            handler[v] = 0;
        }
        return handler;
    }
    var getKeys = function (req) {
        let ret = {
        };
        if (req.id) {
            ret.col = "id";
            ret.ids = req.id.toString().split(';').map(Number);
            return ret;
        } else if (req.uid) {
            ret.col = "uid";
            ret.ids = req.uid.toString().split(';').map(Number);
            return ret;
        } else if (req.name) {
            ret.col = "name";
            ret.ids = req.name.toString().split(';');
            return ret;
        }
        return ret;
    }
    let mysqlCfg = getMysqlCfg(req);
    const conn = mysql.createConnection(mysqlCfg);

    const table = req.table;
    const handler = getHandler(req);
    const idObj = getKeys(req);

    let ret = await selectByIds(conn, table, idObj.col, idObj.ids, handler);
    conn.end();
    return ret;
}

var selectByIds = async function (client, tName, col, ids, handler) {
    var selectFunc = function (tName, obj) {
        obj.id = 0;
        if (tName == 't_user') {
            obj.uid = 0;
            obj.name = 0;
        }
        let sql = 'select ';
        for (let col in obj) {
            sql += col + ',';
        }
        sql = sql.substr(0, sql.length - 1);
        sql += ' from ' + tName;
        return sql;
    }
    var getSql = function (tName, handler, col, ids) {
        let s = selectFunc(tName, handler);
        if ('number' == (typeof (ids[0]))) {
            s += ' where ' + col + ' in (';
            for (let v of ids) {
                s += v + ',';
            }
            s = s.substr(0, s.length - 1);
            s += ')';
            return s;
        } else {
            //s += ' where ' + col + ' like \'%' + ids[0] + '%\'';
            s += ' where ' + col + ' =\'' + ids[0] + '\'';
            return s;
        }
    }
    if (!ids || !ids.length || !col)
        return "params error";

    let s = getSql(tName, handler, col, ids);
    console.log(s);
    let replies = await new Promise((resolve) => {
        client.query(s, [], (err, replies) => {
            if (err) {
                console.log(err);
                return resolve("select error");
            }
            resolve(replies);
        });
    });
    for (let reply of replies) {
        for (let col in reply) {
            if (reply[col] instanceof Object) {
                let data = buffInflate(new Buffer(reply[col], 'Base64'));
                data = JSON.parse(data);
                reply[col] = data;
            }
        }
    }
    return replies[0];
}

var updateByObj = async function (client, tName, handler) {
    var update = async function (client, tName, obj) {
        if (!obj.id)
            return "params error";
        let s = 'update ' + tName + ' set ';
        for (let k in obj) {
            s += k + '=?,'
        }
        s = s.substr(0, s.length - 1);
        s += ' where id=' + obj.id;
        let params = [];
        for (let k in obj) {
            params.push(obj[k]);
        }
        let data = await new Promise((resolve) => {
            client.query(s, params, (err, data) => {
                if (err) {
                    console.log(err);
                    return resolve("db update fail");
                }
                return resolve("db update success");
            });
        });
        return data;
    }
    for (let col in handler) {
        if (handler[col] instanceof Object) {
            let obj = handler[col];
            handler[col] = strDeflate(JSON.stringify(obj));
        }
    }
    let data = await update(client, tName, handler);
    return data;
}

var updateSql = async function (req) {
    const mysqlCfg = getMysqlCfg(req);
    const conn = mysql.createConnection(mysqlCfg);

    const table = req.table;
    const content = req.content;
    let data = await updateByObj(conn, table, content);
    conn.end();

    return data;
}

var sqlExec = async function (req) {
    const mysqlCfg = getMysqlCfg(req);
    const conn = mysql.createConnection(mysqlCfg);
    let data = await new Promise((resolve) => {
        conn.query(req.sqlSentence, [], (err, data) => {
            if (err) {
                console.log(err);
                return resolve("sqlExec error");
            }
            resolve(data);
         });
    });
    conn.end();
    return data;
}

module.exports = {
    selectSql: selectSql,
    updateSql: updateSql,
    sqlExec: sqlExec,
};